/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao.impl;

import dao.intf.DAO;
import database.ConnectionUtil;
import database.UtilDB;
import entities.UserVO;
import entities.VO;
import exceptions.DBException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import org.apache.log4j.Logger;
import utils.LogUtils;


/**
 *
 * @author Valentina
 */
public class UserDAOImpl implements DAO {

    Logger logger = LogUtils.getLogger(this.getClass().getName());
    
    private final String SQL_SAVE = "INSERT INTO USER VALUES (NULL, ?,?,?,?)";
    private final String SQL_LAST_ID = "SELECT MAX(IDUSER) FROM USER";
    
    private final String SQL_UPDATE = "UPDATE USER SET NAME=?, LOGIN=?, PASSWORD=?, IDGROUP=? WHERE IDUSER=?";
    private final String SQL_RESET_GROUP = "UPDATE USER SET IDGROUP=0 WHERE IDGROUP=?";
    
    private final String SQL_DELETE = "DELETE FROM user WHERE iduser=?";
    
    private final String SQL_FIND_BY_ID = "SELECT * FROM USER WHERE IDUSER=?";
    private final String SQL_FIND_ALL = "SELECT * FROM USER ORDER BY IDUSER";
    private final String SQL_FIND_BY_NAME = "SELECT * FROM USER  WHERE NAME=?";

    /**
     *  Method Create
     */
    
    @Override
    public Long create(VO valueObject) {
        
        UserVO userVO = (UserVO) valueObject;
        Long maxId = 0L;

        Connection con = ConnectionUtil.getConnection();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            
            con.setAutoCommit(false);
 
            stmt = con.prepareStatement(SQL_SAVE);

            stmt.setString(1, userVO.getName());
            stmt.setString(2, userVO.getLogin());
            stmt.setString(3, userVO.getPassword());
            stmt.setLong(4, userVO.getIdGroup());

            stmt.executeUpdate();

            stmt = con.prepareStatement(SQL_LAST_ID);
            rs = stmt.executeQuery();
            if (rs.next()) {
                maxId = rs.getLong(1);
            }

            con.commit();
            
        } catch (SQLException e) {
            try {
                maxId = 0L;
                con.rollback();
                throw new DBException("I'm sorry. Create query did not work.");
            } catch (DBException ex) {
                logger.error(ex.getMessage());
            } catch (SQLException ex) {
                logger.error(ex.getNextException().getMessage());
            }

        }finally{
            try {
                UtilDB.closeConnection(con);
                stmt.close();
                rs.close();
            } catch (SQLException ex) {
                logger.error(ex.getNextException().getMessage());
            }
        }
        
        return maxId;
    }

     /**
     *  Method Update
     */
    
    @Override
    public Boolean update(VO valueObject) {
        
        UserVO userVO = (UserVO) valueObject;
        Connection con = ConnectionUtil.getConnection();
        PreparedStatement stmt = null;
        
        try {
            stmt = con.prepareStatement(SQL_UPDATE);

            stmt.setString(1, userVO.getName());
            stmt.setString(2, userVO.getLogin());
            stmt.setString(3, userVO.getPassword());
            stmt.setLong(4, userVO.getIdGroup());
            stmt.setLong(5, userVO.getId());

            stmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            try {
                logger.error(e.getNextException().getMessage());
                throw new DBException("I'm sorry. Update query did not work.");
            } catch (DBException ex) {
                logger.error(ex.getMessage());
            }
            
            return false;
            
        }finally{
            try {
                UtilDB.closeConnection(con);
                stmt.close();
            } catch (SQLException ex) {
                logger.error(ex.getNextException().getMessage());
            }
        }
    }

     /**
     *  Method Delete
     */
    @Override
    public Boolean delete(Long id) {
        Connection con = ConnectionUtil.getConnection();
        PreparedStatement stmt = null;
         try {
            stmt = con.prepareStatement(SQL_DELETE);
            stmt.setLong(1, id);
            stmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            logger.error(e.getNextException().getMessage());
            return false;
        }finally{
            try {
                UtilDB.closeConnection(con);
                stmt.close();
            } catch (SQLException ex) {
                logger.error(ex.getNextException().getMessage());
            }
        }
    }

     /**
     *  Method Retrieve
     */
    @Override
    public VO retrieve(Long id) {
        
        UserVO valueObject = null;
        Connection con = ConnectionUtil.getConnection();
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            
            stmt = con.prepareStatement(SQL_FIND_BY_ID);
            stmt.setLong(1, id);

            rs = stmt.executeQuery();

            if (rs.next()) {
                Long iddb = rs.getLong(1);
                String name = rs.getString(2);
                String login = rs.getString(3);
                String password = rs.getString(4);
                Long idGroup = rs.getLong(5);
                valueObject = new UserVO(iddb, name, login, password, idGroup);

            }
        } catch (SQLException e) {
            logger.error(e.getNextException().getMessage());
        }finally{
            try {
                UtilDB.closeConnection(con);
                stmt.close();
                rs.close();
            } catch (SQLException ex) {
                logger.error(ex.getNextException().getMessage());
            }
            
        }

        return valueObject;
    }

     /**
     *  Method Retrieve
     */
    @Override
    public Collection<? extends VO> retrieve() {
        
        Collection<UserVO> coll = null;
        UserVO valueObject = null;
        Connection con = ConnectionUtil.getConnection();
        
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = con.prepareStatement(SQL_FIND_ALL);

            rs = stmt.executeQuery();

            coll = new ArrayList<UserVO>();

            while (rs.next()) {
                Long iddb = rs.getLong(1);
                String name = rs.getString(2);
                String login = rs.getString(3);
                String password = rs.getString(4);
                Long idGroup = rs.getLong(5);
                valueObject = new UserVO(iddb, name, login, password, idGroup);
                coll.add(valueObject);

            }
        } catch (SQLException e) {
            logger.error(e.getNextException().getMessage());
        }finally{
            try {
                UtilDB.closeConnection(con);
                stmt.close();
                rs.close();
            } catch (SQLException ex) {
                logger.error(ex.getNextException().getMessage());
            }
        }
        return coll;
    }
    
    public Boolean isUnic(String name) {

        Connection con = ConnectionUtil.getConnection();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        int rowCount =0;
        try {
            
            stmt = con.prepareStatement(SQL_FIND_BY_NAME);
            stmt.setString(1, name);

            rs = stmt.executeQuery();

            rs.last();
            rowCount = rs.getRow();
         
        } catch (SQLException e) {
            logger.error(e.getNextException().getMessage());
        }finally{
            try {
                UtilDB.closeConnection(con);
                stmt.close();
                rs.close();
            } catch (SQLException ex) {
                logger.error(ex.getNextException().getMessage());
            }
        }

        if(rowCount>0){
            return false;
        }else{
            return true;
        }
        
    }
    
    
    /**
     *  Method Reset Group ID
     */
    
    public Boolean resetGroup(Long idGroup) {
        
        Connection con = ConnectionUtil.getConnection();
        PreparedStatement stmt = null;
        
        try {
           
            stmt = con.prepareStatement(SQL_RESET_GROUP);
            stmt.setLong(1, idGroup);

            stmt.executeUpdate();
            return true;

        } catch (SQLException e) {
            logger.error(e.getNextException().getMessage());
            return false;
        }finally{
            try {
                UtilDB.closeConnection(con);
                stmt.close();
            } catch (SQLException ex) {
                logger.error(ex.getNextException().getMessage());
            }
        }
    }

}